package com.eorchis.module.classdept.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.eorchis.core.basedao.dao.IDaoSupport.QueryStringType;
import com.eorchis.core.basedao.dao.impl.HibernateAbstractBaseDao;
import com.eorchis.core.basedao.entity.IBaseEntity;
import com.eorchis.core.basedao.query.condition.IPagingQueryCondition;
import com.eorchis.core.basedao.query.condition.builder.CompareType;
import com.eorchis.core.basedao.query.condition.builder.IConditionBuilder;
import com.eorchis.core.basedao.query.condition.builder.impl.DefaultQueryConditionBuilder;
import com.eorchis.core.page.commond.SortInfoBean;
import com.eorchis.core.ui.commond.IQueryCommond;
import com.eorchis.module.classdept.dao.IClassDeptDao;
import com.eorchis.module.classdept.domain.ClassDept;
import com.eorchis.module.classdept.domain.ClassDeptQueryBean;
import com.eorchis.module.classdept.ui.commond.ClassDeptQueryCommond;
import com.eorchis.module.classdept.ui.commond.ClassDeptValidCommond;
import com.eorchis.utils.utils.PropertyUtil;

/**
 * Title: ClassDeptDaoImpl<br>
 * Description: 班级部门关系表数据接口实现<br>
 * Company: GoldGov<br>
 * Copyright @ 2012 GoldGov .All rights reserved.<br>
 * @author Max
 * @createDate 2014-12-30
 * @version $Revision$
 */
@SuppressWarnings("unchecked")
@Repository(value = "com.eorchis.module.classdept.dao.impl.ClassDeptDaoImpl")
public class ClassDeptDaoImpl
					extends HibernateAbstractBaseDao
					implements IClassDeptDao {
	@Autowired
	@Qualifier("jdbcTemplate")
	private JdbcTemplate jdbcTemplate;
	
	public Class<? extends IBaseEntity> entityClass() {
		return ClassDept.class;
	}
	
	public void queryConditionProcessor(
			IConditionBuilder defaultQueryConditionBuilder,
			IQueryCommond queryCommond) {
		ClassDeptQueryCommond classDeptQueryCommond = (ClassDeptQueryCommond)queryCommond;
		defaultQueryConditionBuilder.setBaseQueryString("SELECT t FROM ClassDept t");
		defaultQueryConditionBuilder.addCondition("t.classDeptId", CompareType.IN, classDeptQueryCommond.getSearchClassDeptIds());
		defaultQueryConditionBuilder.addCondition("t.classDeptId", CompareType.EQUAL,classDeptQueryCommond.getSearchClassDeptId());
		defaultQueryConditionBuilder.addCondition("t.trainingClass.classId", CompareType.EQUAL,classDeptQueryCommond.getSearchClassId());
		defaultQueryConditionBuilder.addCondition("t.signupDept.depid", CompareType.EQUAL,classDeptQueryCommond.getSearchDepId());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.HQL);
	}
	
	/**
	 * 查询未选部门列表
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-12-30
	 */
	@Override
	public List<ClassDeptQueryBean> findNoSeleDeptList(
			ClassDeptQueryCommond qCommond) throws Exception {

		Map<String, Object> values = new HashMap<String, Object>();
		StringBuffer shql = new StringBuffer();
		shql.append(" select d.depid as \"depId\",d.DEPNAME as \"deptName\" ");
		shql.append(" from SIGNUP_DEPT d ");
		
		StringBuffer countHql = new StringBuffer(" from SIGNUP_DEPT d ");
		StringBuffer hql = new StringBuffer();
		hql.append(" where 1=1 ");
		if (qCommond.getSearchDepId() != null
				&& !"".equals(qCommond.getSearchDepId())) {
				hql.append(" and d.parent_id=:parentID");
				values.put("parentID", qCommond.getSearchDepId());
		}

		if (qCommond.getSearchClassId() != null
				&& !qCommond.getSearchClassId().equals("")) {
			hql.append(" and d.depid not in( select tcd.DEPID from TRAINING_CLASS_DEPT tcd where tcd.CLASS_ID=:classId )");
			values.put("classId", qCommond.getSearchClassId());
		}
		if (qCommond.getSearchDeptName() != null
				&& !"".equals(qCommond.getSearchDeptName())) {
			hql.append(" and d.DEPNAME like :deptName");
			values.put("deptName", "%" + qCommond.getSearchDeptName().trim()+ "%");
		}

		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(ClassDeptQueryBean.class);

		countHql.append(hql);
		defaultQueryConditionBuilder.setBaseQueryString(countHql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder
				.buliderQueryCondition();
		queryCondition.setParameters(values);
		queryCondition.setQueryStringType(QueryStringType.SQL);
		// 查询总数量
		Long count = this.count(queryCondition);
		int firstResult = qCommond.calculate(count.longValue());
		shql.append(hql);
		List<SortInfoBean> sortInfoList = qCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			shql.append(" order by "
					+ PropertyUtil.sqlInjectionFilter(sortInfo.getProperty())
					+ " "
					+ PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
		} else {
			shql.append(" order by d.ORDER_NUM ");
		}
		defaultQueryConditionBuilder.setBaseQueryString(shql.toString());
		IPagingQueryCondition queryCondition1 = (IPagingQueryCondition) defaultQueryConditionBuilder
				.buliderQueryCondition();
		queryCondition1.setParameters(values);
		queryCondition1.setQueryStringType(QueryStringType.SQL);
		List<ClassDeptQueryBean> list = (List<ClassDeptQueryBean>) this
				.findList(queryCondition1, firstResult, qCommond.getLimit());
		return list;
	}
	
	/**
	 * 查询班级已选部门列表
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-12-30
	 */
	@Override
	public List<ClassDeptQueryBean> findSeleDeptList(
			ClassDeptQueryCommond qCommond) throws Exception {
		StringBuffer shql = new StringBuffer();
		shql.append("select tcd.CLASS_DEPT_ID as \"classDeptId\",tcd.depid as \"depId\",sd.DEPNAME as \"deptName\",tcd.limit_num as \"limitNumBig\" ");
		shql.append(" from TRAINING_CLASS_DEPT tcd,SIGNUP_DEPT sd");
		// 查询数量的sql
		StringBuffer countHql = new StringBuffer("select count(*) from TRAINING_CLASS_DEPT tcd,SIGNUP_DEPT sd");
		// 查询条件的sql
		StringBuffer hql = new StringBuffer();
		hql.append(" where 1=1");
		hql.append(" and tcd.DEPID=sd.DEPID");

		Map<String, Object> values = new HashMap<String, Object>();
		if (qCommond.getSearchClassId() != null
				&& !"".equals(qCommond.getSearchClassId())) {
			hql.append(" and tcd.CLASS_ID=:id");
			values.put("id", qCommond.getSearchClassId());
		}
		
		if (qCommond.getSearchDeptName() != null
				&& !"".equals(qCommond.getSearchDeptName())) {
			hql.append(" and sd.DEPNAME like :deptName");
			values.put("deptName", "%" + qCommond.getSearchDeptName().trim()+ "%");
		}
		
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(ClassDeptQueryBean.class);
		countHql.append(hql);
		// 查询总数量
		List<BigDecimal> executeFind = (List<BigDecimal>) this.executeFind(
				QueryStringType.SQL, countHql.toString(), values);
		Long count = Long.valueOf(executeFind.get(0).toString());
		// 查询总数量
		// 计算分页
		int firstResult = qCommond.calculate(count.longValue());
		// 计算分页

		// 执行查询
		// 数据列表查询
		shql.append(hql);
		// 拼写排序的sql begin
		List<SortInfoBean> sortInfoList = qCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			shql.append(" order by "
					+ PropertyUtil.sqlInjectionFilter(sortInfo.getProperty())
					+ " "
					+ PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
		} else {
			shql.append(" order by sd.ORDER_NUM ");
		}

		defaultQueryConditionBuilder.setBaseQueryString(shql.toString());
		IPagingQueryCondition queryCondition1 = (IPagingQueryCondition) defaultQueryConditionBuilder
				.buliderQueryCondition();
		queryCondition1.setParameters(values);
		queryCondition1.setQueryStringType(QueryStringType.SQL);
		List<ClassDeptQueryBean> list = (List<ClassDeptQueryBean>) this
				.findList(queryCondition1, firstResult, qCommond.getLimit());

		return list;
	}
	
	/**
	 * 查询班级已选部门列表
	 * @param qCommond
	 * @return
	 * @throws Exception
	 * @author ShenPS
	 * @date 2015年1月8日
	 */
	public List<ClassDeptQueryBean> getClassSeleDepList(ClassDeptQueryCommond qCommond) throws Exception{
		StringBuffer sql = new StringBuffer();
		sql.append("select tcd.CLASS_DEPT_ID as \"classDeptId\",tcd.depid as \"depId\",tcd.limit_num as \"limitNumBig\" ");
		sql.append(" from TRAINING_CLASS_DEPT tcd");
		sql.append(" where 1=1");
		Map<String, Object> values = new HashMap<String, Object>();
		if (qCommond.getSearchClassId() != null
				&& !"".equals(qCommond.getSearchClassId())) {
			sql.append(" and tcd.CLASS_ID=:id");
			values.put("id", qCommond.getSearchClassId());
		}	
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(ClassDeptQueryBean.class);
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		IPagingQueryCondition queryCondition1 = (IPagingQueryCondition) defaultQueryConditionBuilder
				.buliderQueryCondition();
		queryCondition1.setParameters(values);
		queryCondition1.setQueryStringType(QueryStringType.SQL);
		List<ClassDeptQueryBean> list = (List<ClassDeptQueryBean>) this.findList(queryCondition1);
		return list;
	}	
	/**
	 * 班级添加报名单位
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-12-31
	 */
	@Override
	public boolean addDeptForClass(
			final ClassDeptValidCommond vCommond) throws Exception {
		return jdbcTemplate.execute(new ConnectionCallback<Boolean>() {
			public Boolean doInConnection(Connection conn) throws SQLException,
					DataAccessException {
				try {
					if (vCommond.getAddDepIds() != null
							&& vCommond.getAddDepIds().length > 0) {
						StringBuffer sql = new StringBuffer();
						sql.append("insert into training_class_dept ");
						sql.append(" (CLASS_DEPT_ID,CLASS_ID,DEPID,LIMIT_NUM)");
						sql.append(" values (?,?,?,?)");
						PreparedStatement pst = conn.prepareStatement(sql.toString());
						for (int i = 0; i < vCommond.getAddDepIds().length; i++) {
							pst.setString(1, UUID.randomUUID().toString().replace("-", ""));
							pst.setString(2, vCommond.getSearchClassId());
							pst.setString(3, vCommond.getAddDepIds()[i]);
							pst.setInt(4,0);
							pst.addBatch();
						}
						pst.executeBatch();
						pst.close();
						return true;
					} else {
						return true;
					}
				} catch (Exception e) {
					e.printStackTrace();
					return false;
				}
			}
		});
	}
	
	
	/**
	 * 删除班级报名单位关系
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-12-31
	 */
	@Override
	public void deleteClassDept(ClassDeptValidCommond vCommond)
			throws Exception {
		String[] linkIds = vCommond.getSearchClassDepIds();
		Map<String, Object> values = new HashMap<String, Object>();
		if (linkIds != null && linkIds.length > 0) {
			String hql = "delete from ClassDept cd where cd.classDeptId in(:linkId)";
			values.put("linkId", linkIds);
			this.executeUpdate(QueryStringType.HQL, hql, values);
		}
	}

	/**
	 * 删除全部的班级和人员关系
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-12-31
	 */
	@Override
	public void delDeptByClassId(ClassDeptValidCommond vCommond)
			throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		if (vCommond.getSearchClassId() != null) {
			String hql = "delete from ClassDept cd where cd.trainingClass.classId =:classId";
			values.put("classId", vCommond.getSearchClassId());
			this.executeUpdate(QueryStringType.HQL, hql, values);
		}
	}
	
	/**
	 * 查询班级已选部门列表中报名已分配的总人数
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @createDate 2015-1-23
	 */
	@Override
	public Integer findSeleDeptNumList(
			ClassDeptQueryCommond qCommond) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("select  sum(tcd.limit_num) from TRAINING_CLASS_DEPT tcd, SIGNUP_DEPT sd where tcd.DEPID = sd.DEPID ");
		Map<String, Object> values = new HashMap<String, Object>();
		if (qCommond.getSearchClassId() != null
				&& !"".equals(qCommond.getSearchClassId())) {
			sql.append(" and tcd.CLASS_ID=:id");
			values.put("id", qCommond.getSearchClassId());
		}
		
		if(qCommond.getSearchNoDepId()!=null&&!"".equals(qCommond.getSearchNoDepId())) {
			sql.append(" and tcd.depid not in(:deid)");
			values.put("deid", qCommond.getSearchNoDepId());
		}
		
		if (qCommond.getSearchDeptName() != null
				&& !"".equals(qCommond.getSearchDeptName())) {
			sql.append(" and sd.DEPNAME like :deptName");
			values.put("deptName", "%" + qCommond.getSearchDeptName().trim()+ "%");
		}
		
		List<BigDecimal> list=(List<BigDecimal>) this.executeFind(QueryStringType.SQL, sql.toString(), values);
		if(list!=null&&list.size()>0) {
			BigDecimal l=list.get(0);
			if(l==null) {
				return 0;
			}
			return Integer.parseInt(l.toString());
		}
		
		return 0;
	}
	
	
	
	/**
	 * 查询班级指定单位的剩余的其它的余额
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @createDate 2015-1-28
	 */
	@Override
	public Integer findSeleNumListByDepId(String classId,String depId,String year,String searchClassType) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("select  sum(tcd.limit_num) from TRAINING_CLASS_DEPT tcd, SIGNUP_DEPT sd ,training_class c where tcd.DEPID = sd.DEPID and tcd.class_id = c.class_id ");
		Map<String, Object> values = new HashMap<String, Object>();
		if (classId != null	&& !"".equals(classId)) {
			sql.append(" and tcd.CLASS_ID!=:id");
			values.put("id", classId);
		}
		
		if(depId!=null&&!"".equals(depId)) {
			sql.append(" and tcd.depid=:deid");
			values.put("deid", depId);
		}
		
		if(year!=null&&!"".equals(year)) {
			sql.append(" and c.classname_year=:year");
			values.put("year", year);
		}
		
		if(searchClassType!=null&&searchClassType.equals("2")) {
			sql.append(" and c.class_type='gfb'");
		}else {
			sql.append(" and c.class_type!='gfb'");
		}

		
		List<BigDecimal> list=(List<BigDecimal>) this.executeFind(QueryStringType.SQL, sql.toString(), values);
		if(list!=null&&list.size()>0) {
			BigDecimal l=list.get(0);
			if(l==null) {
				return 0;
			}
			return Integer.parseInt(l.toString());
		}
		
		return 0;
	}
	
}
